package iot.wlink.utils;

import cn.hutool.core.util.ObjectUtil;

import java.util.Collection;

/**
 * @author : willian fu
 * @version : 1.0
 */
public class SqlUtil {

    /**
     * 防止sql注入
     *
     * @param input
     * @return
     */
    public static String safeString(String input) {
        if (ObjectUtil.isNotNull(input)){
            if (input.indexOf("/*") > 0 && input.indexOf("*/") > 0) {
                return "";
            }
            return input.trim().replaceAll("\\\\s*", "");
        }
        return null;
    }

    public static class SqlCondBuilder {

        private StringBuilder condition;

        public SqlCondBuilder() {
            condition = new StringBuilder();
        }

        public SqlCondBuilder(boolean where) {
            condition = new StringBuilder(where ? " WHERE" : "");
        }

        /**
         * 模糊查询
         *
         * @param field 字段
         * @param val   匹配规则
         * @return SqlCondBuilder
         */
        public SqlCondBuilder andLike(String field, Object val, Like place) {
            this.condition.append(bfWhereAnd()).append(field).append(" LIKE ");
            if (place.equals(Like.LEFT)) {
                this.condition.append("'%").append(val).append("'");
            } else if (place.equals(Like.RIGHT)) {
                this.condition.append("'").append(val).append("%'");
            } else if (place.equals(Like.ALL)) {
                this.condition.append("'%").append(val).append("%'");
            }
            return this;
        }

        /**
         * 模糊查询，值为空则放弃条件
         *
         * @param field 字段
         * @param val   匹配规则
         * @return SqlCondBuilder
         */
        public SqlCondBuilder andLikeNs(String field, Object val, Like place) {
            if (ObjectUtil.isNotNull(val) && ObjectUtil.isNotNull(place) && !"".equals(val)) {
                if (val instanceof String){
                    val = ((String) val).trim();
                }
                this.condition.append(bfWhereAnd()).append(field).append(" LIKE ");
                if (place.equals(Like.LEFT)) {
                    this.condition.append("'%").append(val).append("'");
                } else if (place.equals(Like.RIGHT)) {
                    this.condition.append("'").append(val).append("%'");
                } else if (place.equals(Like.ALL)) {
                    this.condition.append("'%").append(val).append("%'");
                }
            }
            return this;
        }

        public SqlCondBuilder andNs(String val) {
            if (ObjectUtil.isNotNull(val)) {
                this.condition.append(bfWhereAnd()).append(val);
            }
            return this;
        }

        /**
         * 构造in查询条件
         * @param isIn 是in 还是not in
         * @param field 字段
         * @param collection 集合
         * @return SqlCondBuilder
         */
        public SqlCondBuilder andInOrNot(boolean isIn, String field, Collection<?> collection){
            if (collection.size() > 0){
                this.condition.append(bfWhereAnd()).append(field).append(isIn ? " IN ":" NOT IN ").append(" (");
                collection.forEach(c -> this.condition.append("'").append(c).append("',"));
                this.condition.deleteCharAt(this.condition.length() - 1);
                this.condition.append(") ");
            }
            return this;
        }

        /**
         * boolean条件,为空则忽略条件
         * @param field 字段
         * @param t 为真要执行的条件
         * @param f 为假要执行的条件
         * @return
         */
        public SqlCondBuilder andCheckNull(Boolean field, String t, String f) {
            if (ObjectUtil.isNotNull(field)) {
                this.condition.append(bfWhereAnd()).append(field ? t : f);
            }
            return this;
        }

        /**
         * AND 查询
         *
         * @param val 值
         * @return SqlCondBuilder
         */
        public SqlCondBuilder orderBy(String... val) {
            this.condition.append(" ORDER BY ");
            for (String od : val) {
                this.condition.append(od).append(",");
            }
            if (val.length > 0) {
                this.condition.deleteCharAt(this.condition.length() - 1);
            }
            return this;
        }

        /**
         * AND 查询
         *
         * @param field 字段
         * @param val   值
         * @return SqlCondBuilder
         */
        public SqlCondBuilder andEquals(String field, Object val) {
            this.condition.append(bfWhereAnd()).append(field).append(" = ").append(val);
            return this;
        }

        /**
         * AND 查询
         *
         * @param field 字段
         * @param val   值
         * @return SqlCondBuilder
         */
        public SqlCondBuilder andEqualsNs(String field, Object val) {
            if (ObjectUtil.isNotNull(val)) {
                this.condition.append(bfWhereAnd()).append(field).append(" = '").append(val).append("'");
            }
            return this;
        }

        /**
         * 手动条件添加
         *
         * @param val 条件值
         * @return SqlCondBuilder
         */
        public SqlCondBuilder append(Object val) {
            this.condition.append(val);
            return this;
        }

        /**
         * 动态条件，如果val为null，此条件失效
         *
         * @param field 字段
         * @param value 值
         * @return SqlCondBuilder
         */
        public SqlCondBuilder activeCondition(String field, String symbol, Object value) {
            if (ObjectUtil.isNotNull(value)) {
                if (value instanceof String){
                    value = ((String) value).trim();
                }
                this.condition.append(field).append(" ").append(symbol).append(" '").append(value).append("'");
            }
            return this;
        }

        public String build() {
            String conditions = this.condition.toString();
            return "WHERE".equalsIgnoreCase(conditions.trim()) ? "" : conditions;
        }

        /**
         * 判断前面是不是直接为 where，是的话不要and
         * @return
         */
        private String bfWhereAnd(){
            return this.condition.toString().trim().endsWith("WHERE") ? " " : " AND ";
        }

    }

    /**
     * 构造查询条件
     * @param where 是否构造where 用于前面sql没有where的情况
     * @return SqlCondBuilder
     */
    public static SqlCondBuilder builder(boolean where) {
        return new SqlCondBuilder(where);
    }

    public static SqlCondBuilder builder() {
        return new SqlCondBuilder();
    }

    /**
     * Like条件
     */
    public enum Like {
        LEFT,
        RIGHT,
        ALL;

        Like() {
        }
    }

}
